import urllib.request as request
import os
import re
import zipfile
import pandas as pd
import xlrd
from unrar import rarfile
import traceback
import MySQLdb
import datetime

DOWNLOAD_FILE_NAME = 'download_url.txt'


class PriceExcelRecord:

    def __init__(self, batch_id, acreage, price, _30pay, _40pay, room_no, total):
        self.batch_id = batch_id
        self.acreage = acreage
        self.price = price
        self._30pay = _30pay
        self._40pay = _40pay
        self.room_no = room_no
        self.total = total


class PriceExcel:
    def __init__(self, id, attachment_id, status, success, file_name, file_path, date):
        self.file_name = file_name
        self.file_path = file_path
        self.date = date
        self.attachment_id = attachment_id
        self.status = status
        self.success = success
        self.id = id


class BatchPrice:

    def __init__(self, date_str, file_path, file_name, outline_file='', content_files=[]):
        self.outline_file = outline_file
        self.content_files = content_files
        self.date = date_str
        self.file_path = file_path
        self.file_name = file_name
        self.contents = []
        self.outline = None
        self.url = None
        self.success = False

    def __call__(self, *args, **kwargs):
        print(self.outline_file, self.content_files, self.date, self.file_path, self.file_name)

    def set_outline_file(self, outline_file):
        self.outline_file = outline_file

    def add_content_file(self, content_file):
        self.content_files.append(content_file)

    def add_content_files(self, content_files):
        self.content_files = self.content_files + content_files

    def set_contents(self, contents):
        self.contents = contents

    def set_outline(self, outline):
        self.outline = outline

    def add_content(self, content):
        self.contents.append(content)

    def set_url(self, url):
        self.url = url


def now_time():
    return datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S %f')


def Schedule(a, b, c):
    '''
        a:已经下载的数据块
        b:数据块的大小
        c:远程文件的大小
   '''
    per = 100.0 * a * b / c
    if per > 100:
        per = 100
    print("#" * int(per))


def find_contents(content, pattern):
    contents = []
    content_ids = []
    while len(content) > 0:
        var1 = re.search(pattern, content)
        if var1 is not None:
            span_value = var1.span()
            content_ids.append(var1.group(1))
            contents.append(content[span_value[0]:span_value[1]])
            content = content[span_value[1]:]
        else:
            break
    return contents, content_ids


def index_data():
    index_url = 'http://wjj.xa.gov.cn/ptl/def/def/index_1285_3936_ci_recid_4416419.html'
    index_href_pattern = 'href="index_1285_3892.jsp\\?trid=(.+?)"'
    index_content = str(request.urlopen(index_url).read())
    index_href_list, content_ids = find_contents(index_content, index_href_pattern)
    print(len(index_href_list))
    for index_href in index_href_list:
        print(index_href)

    return index_href_list, content_ids


def get_download_file_urls(catch_file_url_page):
    down_file_href_pattern = 'href="(/.+?)"'
    down_file_content = str(request.urlopen(catch_file_url_page).read())
    _, content_ids = find_contents(down_file_content, down_file_href_pattern)
    attached_urls = list(map(lambda x: 'http://wjj.xa.gov.cn{}'.format(x), content_ids))
    return attached_urls[0]


def download_files(download_file_urls, dir_path):
    file_paths = []
    file_url_map = {}
    for download_file_url in download_file_urls:
        file_name = str(download_file_url).split("/")[-1:][0]
        file_path = os.path.join(dir_path, file_name)
        if not os.path.exists(file_path):
            request.urlretrieve(download_file_url, file_path, Schedule)
        file_paths.append(file_path)
        file_url_map[file_path] = download_file_url
    return file_paths, file_url_map


def un_zip(file_name):
    """unzip zip file"""
    with zipfile.ZipFile(file_name) as zip_file:
        if os.path.isdir(file_name[:-4]):
            pass
        else:
            os.mkdir(file_name[:-4])
            for names in zip_file.namelist():
                zip_file.extract(names, file_name[:-4])
    return file_name[:-4]


def un_rar(file_name):
    """unrar zip file"""

    file = rarfile.RarFile(file_name)

    if os.path.isdir(file_name[:-4]):
        pass
    else:
        os.mkdir(file_name[:-4])
        file.extractall(file_name[:-4])
    return file_name[:-4]


def extractall_adapter(file_name, url):
    extract_type = file_name.split(".")[-1:][0]
    print(file_name, extract_type)
    if extract_type == 'zip':
        extract_file_name = un_zip(file_name)
        with open(os.path.join(extract_file_name, DOWNLOAD_FILE_NAME), 'w') as f:
            f.write(url)
        os.remove(file_name)
    elif extract_type == 'rar':
        extract_file_name = un_rar(file_name)
        with open(os.path.join(extract_file_name, DOWNLOAD_FILE_NAME), 'w') as f:
            f.write(url)
        os.remove(file_name)
    else:
        raise Exception('文件类型不被支持%s' % extract_type)


def del_file(del_files):
    for file in del_files:
        os.remove(file)


def file_parser(dir):
    array = []
    for son_dir in list(filter(lambda x: os.path.isdir(os.path.join(dir, x)), os.listdir(dir))):
        date_str = son_dir[:8]
        son = BatchPrice(date_str, os.path.join(dir, son_dir), son_dir)
        content_dir = os.listdir(os.path.join(dir, son_dir))
        content_files = list(filter(lambda x: os.path.isdir(os.path.join(os.path.join(dir, son_dir), x)), content_dir))[
            0]
        outline_file = list(filter(lambda x: os.path.isfile(os.path.join(os.path.join(dir, son_dir), x)), content_dir))[
            0]
        content_files = list(
            map(lambda x: os.path.join(os.path.join(son.file_path, content_files), x),
                os.listdir(os.path.join(son.file_path, content_files))))
        son.set_outline_file(os.path.join(son.file_path, outline_file))
        with open(os.path.join(son.file_path, DOWNLOAD_FILE_NAME), 'r') as f:
            son.set_url(str(f.read()))
        son.add_content_files(content_files)
        son()
        array.append(son)
    return array


def floatParse(s):
    result = float(s) if str.isdigit() else -1
    print(result)
    return result


def content_files_parser(batch_price_array, cursor, connect):
    connect.autocommit(False)
    try:
        for batch_price in batch_price_array:
            batch_price.success = False

            success = True
            for var1 in batch_price.content_files:
                [dirname, filename] = os.path.split(var1)
                if filename.startswith('~$'):
                    continue
                filename = filename.split("、")[1] if filename.__contains__("、") else filename
                filename = filename.split(".")[0]

                excel = PriceExcel(None, '', 1, False, filename, var1, batch_price.date)
                insert_update_batch_prices(cursor, excel, batch_price.url)

                book = xlrd.open_workbook(var1)
                for sheet in book.sheets():
                    try:
                        print(var1, filename, sheet.name)
                        df = pd.read_excel(var1, heet_name=sheet.name, names=["room-no", "acreage", "price", "total"],
                                           skiprows=2, usecols=[1, 2, 3, 4]
                                           , converters={'total': float})
                        df["total"] = pd.to_numeric(df["total"], errors='coerce')
                        none_move = (df["total"].isnull()) | (df["total"].apply(lambda x: str(x).isspace()))
                        print(df[~none_move].head)
                        first_pay(df)
                        for index in range(len(df.index)):
                            # batch_id, acreage, price, _30pay, _40pay,room_no
                            price_content = PriceExcelRecord('', df.iat[index, 1], df.iat[index, 2], df.iat[index, 4],
                                                             df.iat[index, 5],
                                                             df.iat[index, 0], df.iat[index, 3])
                            insert_price_record(cursor, price_content, excel)

                        print(df.head)
                    except Exception as e:
                        print(var1, traceback.print_exc())
                        success = False

            excel.success = success
            insert_update_batch_prices(cursor, excel, batch_price.url)
            connect.commit()
    except Exception as e:
        print(traceback.print_exc())
        connect.rollback()


def first_pay(df):
    try:
        df["30%pay"] = df["total"] * 0.3 / 10000
        df["40%pay"] = df["total"] * 0.4 / 10000
        df["total"] = df["total"] / 10000
    except Exception as e:
        print(df["total"], type(df["total"]), traceback.print_exc())


def insert_attachment(cursor, file_path, url, connect):
    [dirname, filename] = os.path.split(file_path)
    cursor.execute("""
        INSERT INTO batch_price_publicity_attachment ( `url`,`file_name`,`file_path`,`update_time`) VALUES('%s', '%s', '%s','%s');
        """ % (url, filename, file_path, now_time()))


def query_attachment(cursor, url):
    cursor.execute("""
        select * from batch_price_publicity_attachment where `url` = '%s'
        """ % url)
    return cursor


def query_publicity_excel(cursor, file_name, date):
    cursor.execute("""
        select * from price_publicity_excel where `date` = '%s' and `file_name` = '%s'
        """ % (date, file_name))
    return cursor


def create_cursor():
    # 打开数据库连接
    connect = MySQLdb.connect("localhost", "root", "123456", "home", charset='utf8')

    # 使用cursor()方法获取操作游标
    cursor = connect.cursor()

    connect.autocommit(True)
    return connect, cursor


def insert_update_batch_prices(cursor, excel, attachment_url):
    if excel.id is None:
        res = query_publicity_excel(cursor, excel.file_name, excel.date)
        if res.rowcount > 0:
            id = res.fetchone()[0]
            cursor.execute("""
                UPDATE  price_publicity_excel SET `success` =  %s, `update_time` = '%s' WHERE `id` = %s
                """ % (excel.success, now_time(),id))
        else:
            id = query_attachment(cursor, attachment_url).fetchone()[0]
            cursor.execute("""
                INSERT INTO price_publicity_excel ( `attachment_id`,`status`,`date`,`file_path`,`file_name`,`success`, `update_time`) VALUES(%s, %s, '%s','%s', '%s',%s, '%s')
                """ % (id, excel.status, excel.date, excel.file_path, excel.file_name, excel.success,now_time()))
    else:
        cursor.execute("""
                UPDATE  price_publicity_excel SET `success` =  %s,  `update_time` = '%s' WHERE `id` = %s
                """ % (excel.success,now_time(), excel.id))


def is_number(s):
    if s is None or str(s) == '' or str(s) == 'nan':
        return False

    try:
        float(s)
        return True
    except ValueError:
        pass

    try:
        import unicodedata
        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass

    return False


def insert_price_record(cursor, price_content, excel):
    res = query_publicity_excel(cursor, excel.file_name, excel.date)
    one = res.fetchone()
    assert one is not None
    if res.rowcount > 0 and (one[6] == 1 or one[6]):
        pass
    if res.rowcount > 0 and not excel.success:
        id = one[0]
        if is_number(price_content.acreage) and is_number(price_content.price) and is_number(
                price_content.total) and is_number(price_content._30pay) and is_number(
            price_content._40pay):
            cursor.execute("""
            INSERT INTO price_content (`batch_id`,`room_no`,`acreage`,`price`,`total`,`30pay`,`40pay`, `update_time`) VALUES(%s, '%s', %s, %s,%s, %s,%s,'%s')
            """ % (id, price_content.room_no, price_content.acreage, price_content.price, price_content.total,
                   price_content._30pay,
                   price_content._40pay,now_time()))


if __name__ == '__main__':

    connect, cursor = create_cursor()
    try:

        index_href_list, content_ids = index_data()
        data_urls = list(map(lambda x: 'http://wjj.xa.gov.cn/ptl/def/def/websac/cat/{}.html'.format(x), content_ids))

        download_file_urls = list(map(get_download_file_urls, data_urls))

        # skip 已经下载的文件
        download_file_urls = list(filter(lambda x: query_attachment(cursor, x).rowcount < 1, download_file_urls))

        file_paths, file_url_map = download_files(download_file_urls, '../data/sample/home/')

        for file_path in file_paths:
            extractall_adapter(file_path, file_url_map[file_path])
            insert_attachment(cursor, file_path, file_url_map[file_path], connect)

        array = file_parser('../data/sample/home/')

        content_files_parser(array, cursor, connect)

    except Exception as e:
        print(traceback.print_exc())

    cursor.close()
    connect.close()
